import pandas as pd
import json 
import cx_Oracle
from pypinyin import pinyin,lazy_pinyin,Style

class RegIon():
   def __init__(self,use,passwd,database):
      connection = cx_Oracle.connect(use,passwd,database,encoding="UTF-8")
      cursor = connection.cursor()
      print('初始化数据连接池成功！')
      self.connection = connection
      self.cursor = cursor

   def normallize(self,name):
     return name.capitalize()

   def addData(self,df):
      for index,prow in df.iterrows():
         self.insertRow(prow['code'],prow['name'],1,1)
         for chrow in prow['children']:
            self.insertRow(chrow['code'],chrow['name'],prow['code'],2)
            for citrow in chrow['children']:
               self.insertRow(citrow['code'],citrow['name'],chrow['code'],3)
               for jiedaoRow in citrow['children']:
                  self.insertRow(jiedaoRow['code'],jiedaoRow['name'],citrow['code'],4)
     

   def insertRow(self,code,name,pid,level):
      namepy = lazy_pinyin(name)
      namelist = list(map(self.normallize,namepy))
      name_en = " ".join(namelist)
      print(name_en)
      insertSql = "insert into CSC.T_S_REGION(ID,NAME,PID,name_en,region_level) values ('{id}','{name}',{pid},'{name_en}','{region_level}')"
      self.cursor.execute(insertSql.format(id=code,name=name,pid=pid,name_en=name_en,region_level=level))
      self.connection.commit()
   
   
   def closedb(self):
      self.connection.close()


def getJsonDf():
   df = pd.read_json(r"E:\Software\Administrative-divisions-of-China\dist\pcas-code.json",encoding="utf-8")
  # print(df.info)
   return df


if __name__ == "__main__":
   region = RegIon('CSC','IoTdev1905csc','10.100.21.122:1521/IOT_CSC')
   df = getJsonDf()
   region.addData(df)
   region.closedb()